package br.com.ffm.praticas.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import br.com.ffm.praticas.bean.*;
import br.com.ffm.praticas.connection.ConnectionDB;

public class SalesItemDAO {
	
	/**
	 * Method that return all the Sell's Products
	 * @param sellId Sell Identifier
	 * @return List<Product> Product List of a specific Sales
	 * @throws SQLException
	 */
	public List<Product> getProducts(int sellId) throws SQLException {
		String query = "SELECT * FROM sales_item WHERE (sell_id = " + sellId + " )";
		ProductDAO productDAO = new ProductDAO();
		List<Product> list = new LinkedList<Product>();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			Product product = productDAO.getProduct(result.getInt("product_id"));
			list.add(product);
		}
		ConnectionDB.getInstance().close();
		return list;
	}
	
	/**
	 * Method that return a Sale resume of a Sell for the Sell Id
	 * @param sellId Sell Identifier
	 * @return List<QtdeXProduct> Resume of a specific Sales
	 * @throws SQLException
	 */
	public List<QtdeXProduct> getResume(int sellId) throws SQLException {
		String query = "SELECT SUM(p.unit_value) as total_value, COUNT( * ) as qtde , p.product_name as product_name FROM sales_item si, product p where (si.sell_id = " + sellId + " and si.product_id = p.id) GROUP BY p.id";
		List<QtdeXProduct> list = new LinkedList<QtdeXProduct>();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			QtdeXProduct qtdeXProduct = new QtdeXProduct();
			qtdeXProduct.setTotalValue(result.getDouble("total_value"));
			qtdeXProduct.setQtdeProducts(result.getInt("qtde"));
			if (result.getString("product_name") != null)
				qtdeXProduct.setProductName(result.getString("product_name"));
			list.add(qtdeXProduct);
		}
		ConnectionDB.getInstance().close();
		return list;
	}
	
	/**
	 * Method that insert a Sales Item
	 * @param sellId Sell Identifier
	 * @param productId Product Identifier
	 * @return true it will have been inserted or false if it will not be inserted
	 */
	public boolean insert(int sellId, int productId) {
		String query = "INSERT INTO sales_item (sell_id, product_id) VALUES (" + sellId + ", " +productId +")";
		try {
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException sqlExcp) {
			sqlExcp.printStackTrace();
			return false;
		}
	}
	
	/**
	 * Method that delete a Sales Item
	 * @param sellId Sell Identifier
	 * @param productId Product Identifier
	 * @return true it will have been deleted or false if it will not be deleted
	 */
	public boolean delete(int sellId, int productId) {
		String query = "DELETE FROM sales_item WHERE (sellId = " + sellId + " and product_id = " + productId + ")";
		try {
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException sqlExcp) {
			sqlExcp.printStackTrace();
			return false;
		}
	}
	
	/**
	 * Method that find and return a SalesItem
	 * @param sellID The id of the sell that references the salesItem
	 * @param productID The id of the product referenced by salesItem
	 * @return If found returns true
	 * @throws SQLException 
	 */
	public boolean getSalesItem(int sellID,int productID) throws SQLException{
		String query = "SELECT * FROM sales_item WHERE sell_id = "+ sellID +" and product_id = "+productID;
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if(result.next()){
			return true;
		}
		return false;
	}
	
	public boolean delete(int sellId) {
		String query = "DELETE FROM sales_item WHERE (sell_Id = " + sellId + " )";
		try {
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException sqlExcp) {
			sqlExcp.printStackTrace();
			return false;
		}
	}

}
